import serial
import pymysql
import time
import re

# 串口配置
ser = serial.Serial('COM4', 9600, timeout=1)  # 请根据实际情况调整端口号和波特率

# MySQL数据库配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '200206',
    'database': 'Temperature_Control_System',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 连接到MySQL数据库
connection = pymysql.connect(**db_config)

def is_valid_temperature(s):
    # 使用正则表达式检查字符串是否只包含数字和一个小数点（可选）
    return bool(re.match(r'^-?\d+(\.\d+)?$', s))

try:
    with connection.cursor() as cursor:
        while True:
            try:
                if ser.in_waiting:
                    # 读取串口数据
                    data = ser.readline().decode().strip()
                    print(f"Received raw data: {data}")

                    # 验证数据是否为有效的温度值
                    if is_valid_temperature(data):
                        try:
                            # 获取当前时间戳
                            current_timestamp = time.strftime('%Y-%m-%d %H:%M:%S')

                            # 插入数据到数据库
                            sql = "INSERT INTO temperature_log (temperature, log_time) VALUES (%s, %s)"
                            cursor.execute(sql, (data, current_timestamp))

                            # 提交事务
                            connection.commit()
                        except pymysql.MySQLError as e:
                            print(f"MySQL error: {e}")
                    else:
                        print(f"Warning: Invalid temperature data received: {data}")
            except serial.SerialException as e:
                print(f"Serial error: {e}")
            except Exception as e:
                print(f"An unexpected error occurred: {e}")

            # 等待一段时间再读取数据
            time.sleep(1)

except KeyboardInterrupt:
    print("Program interrupted by user.")
finally:
    # 关闭数据库连接和串口连接
    if connection.open:
        connection.close()
    ser.close()
    print("Connections closed.")